package com.demo2do.fms.admin.entity.search.business;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 勾选订单送财务
 * 
 * @author Ysy
 */
public class SapSearch implements QueryBuilder{
    
    /** 所选择公司 */
    private Long organizationId;
    
    /** 调度指令号 */
    private String loadNumber;
    
    /** 委托方公司 */
    private Long consignorId;
    
    /** 最终客户 */
    private Long customerId;
    
    /** Shipment开始调度日期 */
    private String beginShipmentDate;
    
    /** Shipment结束调度日期 */
    private String endShipmentDate;
    
    /** 勾选状态 */
    private String collectStatus;
    
    /** 调度指令号列表 */
    private String loadNumbers;
        
    private QueryOrder queryOrder;
    
    private QueryFilter queryFilter;

    /**
     * the default constructor
     */
    public SapSearch() {
        queryOrder = new QueryOrder();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if(queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter();
            
            if (StringUtils.isNotBlank(collectStatus)) {
                queryFilter.addSentence("finance.collect_status = '" + collectStatus + "'", Collections.emptyMap());
            }
            
            queryFilter.addEquals("finance", "consignor_id", consignorId)
                       .addEquals("shipment", "customer_id", customerId)
                       .addEquals("company", "id", organizationId);
                                           
            if (StringUtils.isNotBlank(beginShipmentDate)) {
                queryFilter.addSentence("shipment.shipment_date >= TO_DATE('" + beginShipmentDate.replace("-", "") + "', 'yyyymmdd')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(endShipmentDate)) {
                queryFilter.addSentence("shipment.shipment_date < TO_DATE('" + endShipmentDate.replace("-", "") + "', 'yyyymmdd')  + TO_DSINTERVAL ('001 00:00:00')", Collections.emptyMap());
            }
            
            if (StringUtils.isNotBlank(loadNumbers)) {
                List<String> loadNumberList = this.constructList(loadNumbers);
                if (CollectionUtils.isNotEmpty(loadNumberList)) {
                    queryFilter.addSentence("shipment.load_number IN (:loadNumberList)", "loadNumberList", loadNumberList);
                }
            }
            
            queryFilter.addEquals("shipment", "load_number", loadNumber);
            
        }
        
        return queryFilter;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT DISTINCT ")
                     .append("finance.shipment_finance_id AS shipmentfinanceid, ")
                     .append("shipment.load_number AS loadNumber, ")
                     .append("TO_CHAR(finance.fee_rate, '999999990.99') AS feeRate, ")
                     .append("TO_CHAR(finance.kilometer, '999999990.99') AS kilometer, ")
                     .append("company.organization_name AS organizationName, ")
                     .append("client.organization_name AS clientName, ")
                     .append("TO_CHAR(finance.fee, '999999990.99') AS fee, ")
                     .append("wa.facility_code || ' - ' || wa.facility_name AS srcName, ")
                     .append("wb.facility_code || ' - ' || wb.facility_name AS destName, ")
                     .append("consignor.organization_name AS consignorName, ")
                     .append("cartype.product_category_name AS typeName, ")
                     .append("color.feature_name AS colorName, ")
                     .append("FC_GET_VINCODE_BY_SHIPMENT_ID(shipment.shipment_id) AS vincode, ")
                     .append("FC_GET_ORDER_STATUS(finance.id, finance.shipment_finance_id) AS orderStatus ")
             .append("FROM tbl_all_finance finance, tbl_all_shipment shipment, vw_car_color color, vw_car_category cartype, vw_transportation_company company, vw_client client, vw_consignor consignor, vw_warehouse wa, vw_warehouse wb ")
             .append("WHERE finance.company_id = company.ID AND finance.is_disabled = 0 AND shipment.is_disabled = 0 AND client.ID(+) = shipment.customer_id AND client.is_disabled(+) = 0 AND consignor.id(+) = finance.consignor_id AND consignor.is_disabled(+) = 0 AND wa.ID = finance.src_wh_id AND wb.ID = finance.dest_wh_id AND shipment.vh_cartype_id = cartype.ID AND shipment.vh_color_id = color.ID AND shipment.shipment_id = finance.shipment_id ")
             .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        return buffer.toString();
        
    }
    
    /**
     * build row mapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {

            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("shipmentFinanceId", rs.getObject("shipmentFinanceId"));
                result.put("loadNumber", rs.getObject("loadNumber"));
                result.put("feeRate", rs.getObject("feeRate"));
                result.put("kilometer", rs.getObject("kilometer"));
                result.put("organizationName", rs.getObject("organizationName"));
                result.put("clientName", rs.getObject("clientName"));
                result.put("fee", rs.getObject("fee"));
                result.put("srcName", rs.getObject("srcName"));
                result.put("destName", rs.getObject("destName"));
                result.put("consignorName", rs.getObject("consignorName"));
                result.put("typeName", rs.getObject("typeName"));
                result.put("colorName", rs.getObject("colorName"));
                result.put("vincode", rs.getObject("vincode"));
                result.put("orderStatus", rs.getObject("orderStatus"));
                        
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /*
     * (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /**
     * @return
     */
    public QueryFilter getFilter(){
        return getPreparedFilter(false);
    }
    
    /**
     * @param code
     * @return
     */
    private List<String> constructList(String code) {
        code = StringUtils.replaceAll(code, "\r", "");
        code = StringUtils.replaceAll(code, "\t", "");
        code = StringUtils.replaceAll(code, " ", "");
        String[] temp = StringUtils.split(code, "\n");
        return Arrays.asList(temp);
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }

    /**
     * @return the loadNumber
     */
    public String getLoadNumber() {
        return loadNumber;
    }

    /**
     * @return the consignorId
     */
    public Long getConsignorId() {
        return consignorId;
    }

    /**
     * @return the customerId
     */
    public Long getCustomerId() {
        return customerId;
    }

    /**
     * @return
     */
    public String getBeginShipmentDate() {
        return beginShipmentDate;
    }
    
    /**
     * @return
     */
    public String getEndShipmentDate() {
        return endShipmentDate;
    }
    
    /**
     * @return the collectStatus
     */
    public String getCollectStatus() {
        return collectStatus;
    }

    /**
     * @return
     */
    public String getLoadNumbers() {
        return loadNumbers;
    }
    
    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param loadNumber the loadNumber to set
     */
    public void setLoadNumber(String loadNumber) {
        this.loadNumber = StringUtils.trim(loadNumber);
    }

    /**
     * @param consignorId the consignorId to set
     */
    public void setConsignorId(Long consignorId) {
        this.consignorId = consignorId;
    }

    /**
     * @param customerId the customerId to set
     */
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }

    /**
     * @param beginShipmentDate
     */
    public void setBeginShipmentDate(String beginShipmentDate) {
        this.beginShipmentDate = beginShipmentDate;
    }
    
    /**
     * @param endShipmentDate
     */
    public void setEndShipmentDate(String endShipmentDate) {
        this.endShipmentDate = endShipmentDate;
    }
    
    /**
     * @param collectStatus the collectStatus to set
     */
    public void setCollectStatus(String collectStatus) {
        this.collectStatus = collectStatus;
    }
    
    /**
     * @param loadNumbers
     */
    public void setLoadNumbers(String loadNumbers) {
        this.loadNumbers = loadNumbers;
    }

}
